import pymysql


class LogisticsDB:

    def __init__(self, host='localhost',
                 port=3306,
                 db='logistics',
                 user='root',
                 password='python123!'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password

    def get_connection(self):
        """获取MySQL数据库连接"""
        con = pymysql.connect(host=self.host,
                              port=self.port,
                              db=self.db,
                              user=self.user,
                              password=self.password)
        return con

    def get_products(self):
        """ 返回产品信息 """
        sql = '''SELECT sku_id, sku_name 
                   FROM Products'''

        with self.get_connection() as con:
            cursor = con.cursor()
            results = cursor.execute(sql)
            products = cursor.fetchall()
            product_dict = {}  # 保存产品信息的字典：sku_id:sku_name
            for product in products:
                product_dict[product[0]] = product[1]
        return product_dict

    def get_locations(self):
        """ 返回货架位置信息 """
        sql = '''SELECT loc_id, loc_name 
                   FROM Locations'''

        with self.get_connection() as con:
            cursor = con.cursor()
            results = cursor.execute(sql)
            locations = cursor.fetchall()
            location_dict = {}  # 保存货架位置的字典：loc_id:loc_name
            for location in locations:
                location_dict[location[0]] = location[1]
        return location_dict

    def get_items(self):
        """ 返回商品信息 """
        sql = '''SELECT Items.sku_id, Items.loc_id, sku_name, loc_name 
                   FROM Products, Locations, Items
                  WHERE Items.sku_id = Products.sku_id
                    AND Items.loc_id = Locations.loc_id'''

        with self.get_connection() as con:
            cursor = con.cursor()
            results = cursor.execute(sql)
            items = cursor.fetchall()
            items_list = []  # #保存商品库存的列表，元素为元组(sku_id,loc_id)
            for item in items:
                items_list.append((item[0], item[1]))
        return items_list

    def add_product(self, sku_id, sku_name):
        """ 增加一个产品sku_id、sku_name, 插入一条记录到Products表"""
        sql = '''insert into Products(sku_id, sku_name) 
                    values('{0}','{1}')'''.format(sku_id, sku_name)
        with self.get_connection() as con:
            cursor = con.cursor()
            cursor.execute(sql)
            con.commit()

    def add_location(self, loc_id, loc_name):
        """ 增加一个货架位置loc_id、loc_name, 插入一条记录到Locations表"""
        sql = '''insert into Locations(loc_id, loc_name) 
                    values('{0}','{1}')'''.format(loc_id, loc_name)
        with self.get_connection() as con:
            cursor = con.cursor()
            cursor.execute(sql)
            con.commit()

    def check_item(self, sku_id, loc_id):
        """ 检查商品sku_id、货架sku_id是否在Items表中存在"""
        sql = '''select sku_id, loc_id
                   from Items 
                  where sku_id = '{0}'
                    and loc_id = '{1}' '''.format(sku_id, loc_id)
        with self.get_connection() as con:
            cursor = con.cursor()
            result = cursor.execute(sql)
            items = cursor.fetchone()
            if not items:
                return True
            else:
                return False

    def add_item(self, sku_id, loc_id):
        """ 入库一件商品：商品sku_id、货架sku_id, 插入一条记录到Items表"""
        sql = '''insert into Items(sku_id, loc_id) 
                    values('{0}','{1}')'''.format(sku_id, loc_id)
        with self.get_connection() as con:
            cursor = con.cursor()
            result = cursor.execute(sql)
            con.commit()
            print(result)
            return result

    def remove_item(self, sku_id, loc_id):
        """ 出库一件商品：商品sku_id、货架sku_id, 从Items表中删除一条记录"""
        sql = '''delete from Items
                  where sku_id = '{0}'
                    and loc_id = '{1}' '''.format(sku_id, loc_id)
        with self.get_connection() as con:
            cursor = con.cursor()
            result = cursor.execute(sql)
            con.commit()
            return result